package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;


import com.entity.Report_electri_Power;

/**   
* @Title: Report_electri_PowerDao.java 
* @Package com.interfaces 
* @author 孙梦楠 
* @date 2017年10月23日 上午9:47:24 
* @version V1.0.0   
*/

public interface Report_electri_PowerDao extends JpaRepository<Report_electri_Power, Integer>{
	
	@Transactional
	@Query(value="SELECT * FROM report_electri_power tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress=?1) AND (tri.code=?2) ORDER BY tri.time" ,nativeQuery=true)
	public List<Report_electri_Power> findTaskrecorditemByMeter(Integer meterid, String code,String time1,String time2);
	
	@Transactional
	@Query(value="SELECT * FROM report_electri_power tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress IN (SELECT m.meter_id FROM e_meter m WHERE m.meter_org=?1 AND m.type=?5 AND m.meter_status != 4)) AND (tri.code=?2)" ,nativeQuery=true)
	public List<Report_electri_Power> findTaskrecorditemByMeter2(Integer meterorg, String code,String time1,String time2,String type);

	@Transactional
	@Query(value="SELECT COUNT( DISTINCT tri.meteraddress) FROM report_electri_power tri WHERE (tri.time = ?1 OR  ?1 IS NULL)  "
			+ "AND (tri.meteraddress IN (SELECT m.meter_address FROM e_meter m WHERE m.meter_org=?2 AND m.type=?3 AND m.meter_status != 4)) "
			+ "AND (tri.code=?4) " ,nativeQuery=true)
	public Integer customercountByMeter(String time,Integer meterorg, String type,String code);
	
	List<Report_electri_Power> findByMeteraddress(String meteraddress);
	
	List<Report_electri_Power> findByTimeAndTerminaladdressAndCode(String time,String terminaladdress,String code);
	
	
	@Transactional
    @Modifying
    @Query("delete from Report_electri_Power rep where rep.terminaladdress=?1 AND rep.time=?2 AND rep.code = ?3")
    public void  deletedata(String terminaladdress,String time,String code);
	
	@Transactional
	@Query(value="SELECT sum(value1) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4)) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea1(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT sum(value2) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea2(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT sum(value3) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea3(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT sum(value4) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea4(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT sum(value5) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea5(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT sum(value6) from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Double>  totelvaluearea6(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT * from report_electri_power where code=?1 "
			+" AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL)"
			+ "AND (meteraddress IN(SELECT meter_id from e_meter where meter_org =?4 AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			nativeQuery=true)
	public List<Report_electri_Power>  totelcount(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value1) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany1(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value2) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany2(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value3) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany3(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value4) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany4(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value5) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany5(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT SUM(value6) from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			
			nativeQuery=true)
	public List<Double>  totelvaluecompany6(String code,String time1,String time2,Integer org);
	
	@Transactional
	@Query(value="SELECT * from report_electri_power  where code=?1  AND (time > ?2 OR  ?2 IS NULL) AND (time < ?3 OR ?3 IS NULL) AND (meteraddress IN(SELECT meter_id from e_meter where meter_org IN (SELECT org_value from e_org where org_father=?4) AND meter_id NOT IN (SELECT meter_id from r_meter_childmeter) OR meter_id IN (SELECT meter_id from r_meter_childmeter where type='2') )) GROUP BY time",
			
			nativeQuery=true)
	public List<Report_electri_Power>  totelcount2(String code,String time1,String time2,Integer org);

}
